package daoJDBC.interfaceDO.serviceDO;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.BigClass;
import model.Commodity;
import model.Indent;
import model.Notice;
import model.ShoppingCar;
import model.SmallClass;
import model.User;

import daoJDBC.Conn_IDUS;
import daoJDBC.interfaceDO.Do_Interface;

/**
 * 增删查改的具体实现类
 * @author Administrator
 *
 */
public class Do_IDUS extends Conn_IDUS implements Do_Interface{

	/**
	 * 专用于查询登陆，核实登陆者输入的帐户及密码
	 * @param name--帐户名
	 * @param password--密码
	 * @return list--查询所得结果集，若为NULL则说明无此用户或密码错误，不为NULL则说明有此用户即可登陆成功
	 * @throws SQLException 
	 */
	public ResultSet enterSelect(String ta_name,String aname,String password) {
		//以帐户名和密码为双重条件到数据库中查询，结果集返回NULL则说明此用户不存在或密码错误 
//		List listenter=new ArrayList();
		String sql="";
		if (ta_name.equals("admin")) {//管理员登陆
			sql="select * from admin where adminName=? and password=?";
		}else if (ta_name.equals("user")) {//用户登陆
			sql="select * from user where account=? and password=?";
			System.out.println(aname+"---"+password);
		}
		Object []obj=new Object[]{aname,password};
		dosql(sql, obj);
		ResultSet rs=getRS();
		if (rs!=null) {
//			try {
////				System.out.println("->>>>"+rs.getString(1));
//			} catch (SQLException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
			System.out.println("===+++++++====");
		}else {
			System.out.println("-------");
		}
//		try {
//			System.out.println(rs.next());
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			//e.printStackTrace();
//		}
		
		return rs;
	}
	
	/**
	 * 添加 ，向数据库增加信息，ID为数据库自增长，其它由前台获取而来
	 * @param user 将前台获取的用户各信息创建成一个用户对象传进来
	 * @return k 数据库增加数据返回的一个信息，用于判断数据库的操作是否完成，即用户是否注册成功
	 */
	public int insert(String sql,Object[] obj) {
		int k=-1;
//		String sql="insert into user(name,password,sex,age,address) values(?,?,?,?,?)";
//		Object []obj=new Object[]{
//				user.getName(),
//				user.getPassword(),
//				user.getSex(),
//				user.getAddress()
//		};
		dosql(sql,obj);
		k=getCount();
		toClose();
		return k;
	}
	/**
	 * 删除用户信息的方法
	 * @param id 传入的是用户在数据库中的ID
	 * @return  k 返回的是以作判断是否成功删除的数值
	 */
	public int delete(String ta_name,int id) {
		int k=-1;String sql="";Object []obj=null;
		if(id==0){
			sql="delete from "+ta_name;
			obj=new Object[]{};
		}else {
			sql="delete from "+ta_name+" where id=?";
			obj=new Object[]{id};
		}
		dosql(sql,obj);
		k=getCount();
		toClose();
		return k;
	}
	/**
	 * 修改用户信息的方法
	 * @param obj 传入修改过后 的信息数组，以写入到数据库中去替换原信息
	 * @return  k 返回修改的影响行数，判断是否执行成功
	 */
	public int update(String sql,Object[] obj) {
		int k=-1;
//		String sql="update user set name=?,password=?,sex=?,age=?,address=? where id=?";
//		Object []obj=new Object[]{
//			user.getName(),
//			user.getPassword(),
//			user.getSex(),
//			user.getAddress(),
//			user.getId()
//		};
		dosql(sql,obj);
		k=getCount();
		toClose();
		return k;
	}
	/**
	 * 分页查询方法，每页显示十条信息，运用select * from 表名 limit tpage,6;查询语句
	 * @param ta_name--传入要查询的表名
	 * @param tpage---传入的当前页，用以计算出当前页所需要的结果集是从数据库的第几条信息查起
	 * @return list 返回查到的结果集合，以User的对象形式保存在list集合中
	 */
	public List select(String ta_name,int tpage) {
		List list=new ArrayList();
		String sql="";Object []obj=null;
		if (tpage==0) {
			sql="select * from "+ta_name;
			obj=new Object[]{};
		}else{
			sql="select * from "+ta_name+" limit ?,6";
			obj=new Object[]{
					(tpage-1)*6//例：第一页为limit 0,10  第二页为limit 10,10......以此类推得出计算式
			};
		}
		dosql(sql,obj);
		ResultSet rs=getRS();
		if (rs!=null) {
			try {
				rs.last();
				int i=rs.getRow();
				if (i>0) {
					rs.beforeFirst();
					while(rs.next()){
						if (ta_name.equals("bigclass")){//大分类查询
							int id=rs.getInt("id");
							String bigclassName=rs.getString("bigclassName");
							list.add(new BigClass(id,bigclassName));
						}else if (ta_name.equals("user")){//用户查询
							int id=rs.getInt("id");
							String account=rs.getString("account");
							String name=rs.getString("name");
							String sex=rs.getString("sex");
							String email=rs.getString("email");
							String cellphone=rs.getString("cellphone");
							list.add(new User(id,account, name, sex, email, cellphone));
						}else if (ta_name.equals("smallclass")) {//小分类
							int id=rs.getInt("id");
							String smallclassName=rs.getString("smallclassName");
							String bigclassName=rs.getString("bigclassName");
							list.add(new SmallClass(id,smallclassName,bigclassName));
						}else if (ta_name.equals("notice")) {//公告
							int id=rs.getInt("id");
							String noticeTitle=rs.getString("noticeTitle");
							list.add(new Notice(id, noticeTitle));
						}else if (ta_name.equals("indent")) {//订单
							int id=rs.getInt("id");
							String userName=rs.getString("userName");
							String userAddress=rs.getString("userAddress");
							String indentStatus=rs.getString("indentStatus");
							list.add(new Indent(id, userName, userAddress, indentStatus));
						}else if (ta_name.equals("commodity")) {//商品详情
							int id=rs.getInt("id");
							String commodityName=rs.getString("commodityName");
							String smallclassName=rs.getString("smallclassName");
							double oldSale=rs.getDouble("oldSale");
							double sale=rs.getDouble("sale");
							int num=rs.getInt("num");
							String picture=rs.getString("picture");
							list.add(new Commodity(id, commodityName, smallclassName, oldSale, sale, num, picture));
						}
					}
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		toClose();
		return list;
	}
	public List selectSC(){
		List list=new ArrayList();
		String sql="select * from shoppingcar";
		Object []obj=new Object[]{};
		dosql(sql, obj);
		ResultSet rs=getRS();
		if (rs!=null) {
			try {
				rs.last();
				int i=rs.getRow();
				if (i>0) {
					rs.beforeFirst();
					while (rs.next()) {
						int id=rs.getInt("id");
						String commodityName=rs.getString("commodityName");
						double oldSale=rs.getDouble("oldSale");
						double sale=rs.getDouble("sale");
						double zekou=rs.getDouble("zekou");
						int saleNum=rs.getInt("saleNum");
						list.add(new ShoppingCar(id, commodityName, oldSale, sale, zekou, saleNum));
					}
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		toClose();
		return list;
	}
	/**
	 * 查询单个用户信息
	 * @param uid--用户ID
	 * @return list--保存的用户信息的集合
	 */
	public List selectOne(String ta_name,int thisID){
		List list=new ArrayList();
		String sql="select * from "+ta_name+" where id=?";
		Object []obj=new Object[]{thisID};
		dosql(sql, obj);
		ResultSet rs=getRS();
		if (rs!=null) {
			try {
				rs.next();
				if (ta_name.equals("indent")) {//订单
					int id=rs.getInt("id");
					String userName=rs.getString("userName");
					String userAddress=rs.getString("userAddress");
					double amount=rs.getDouble("amount");
					String indentDate=rs.getString("indentDate");
					String indentStatus=rs.getString("indentStatus");
					list.add(new Indent(id, userName, userAddress, amount, indentDate, indentStatus));
				}else if (ta_name.equals("bigclass")) {//大分类
					int id=rs.getInt("id");
					String bigclassName=rs.getString("bigclassName");
					list.add(new BigClass(id, bigclassName));
				}else if (ta_name.equals("user")) {//用户
					int id=rs.getInt("id");
					String account=rs.getString("account");
					String name=rs.getString("name");
					String password=rs.getString("password");
					String sex=rs.getString("sex");
					String birth=rs.getString("birth");
					String cardID=rs.getString("cardID");
					String email=rs.getString("email");
					String cellphone=rs.getString("cellphone");
					String address=rs.getString("address");
					list.add(new User(id, account, name, password, sex, birth, cardID, email, cellphone, address));
				}else if (ta_name.equals("smallclass")) {//小分类
					int id=rs.getInt("id");
					String smallclassName=rs.getString("smallclassName");
					String bigclassName=rs.getString("bigclassName");
					list.add(new SmallClass(id, smallclassName, bigclassName));
				}else if (ta_name.equals("notice")) {//公告
					int id=rs.getInt("id");
					String noticeTitle=rs.getString("noticeTitle");
					String noticeContent=rs.getString("noticeContent");
					list.add(new Notice(id, noticeTitle, noticeContent));
				}else if (ta_name.equals("commodity")) {//商品
					int id=rs.getInt("id");
					String commodityName=rs.getString("commodityName");
					String smallclassName=rs.getString("smallclassName");
					double oldSale=rs.getDouble("oldSale");
					System.out.println("原价》》》》》"+oldSale);
					double sale=rs.getDouble("sale");
					int num=rs.getInt("num");
					String picture=rs.getString("picture");
					list.add(new Commodity(id, commodityName, smallclassName, oldSale, sale, num, picture));
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		toClose();
		return list;
	}
	/**
	 * 根据条件来查询商品
	 * @param s_c_Name
	 * @return
	 */
	public List selectComm(int k,String str){
		List list=new ArrayList();
		String sql="";
		if(k==1){
			sql="select * from commodity where isnew=?";
		}else if(k==2){
			sql="select * from commodity where smallclassName=?";
		}
		Object []obj=new Object[]{str};
		dosql(sql, obj);
		ResultSet rs=getRS();
		if (rs!=null) {
			try {
				rs.last();
				int i=rs.getRow();
				if (i>0) {
					rs.beforeFirst();
					while (rs.next()) {
					//商品
					int id=rs.getInt("id");
					String commodityName=rs.getString("commodityName");
					String smallclassName=rs.getString("smallclassName");
					double oldSale=rs.getDouble("oldSale");
					System.out.println("原价》》》》》"+oldSale);
					double sale=rs.getDouble("sale");
					int num=rs.getInt("num");
					String picture=rs.getString("picture");
					list.add(new Commodity(id, commodityName, smallclassName, oldSale, sale, num, picture));
					}
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		toClose();
		return list;
	}
	/**
	 * 查询用户总数，即数据库所有的信息条数
	 * @return k--用户的总人数，即用户信息的总条数
	 */
	public int getAllUser(String ta_name){
		int k=0;
		String sql="select count(id) from "+ta_name;
		/*
		 此条查询信息所查询出来的结果集如下，我们所要得到的即为3的值
		 +-----------+
		 | count(id) |
		 +-----------+
		 |         3 |
		 +-----------+
		 */
		dosql(sql, null);
		ResultSet rs=getRS();
		if (rs!=null) {
			try {
				rs.next();
				k=rs.getInt("count(id)");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return k;
	}
	/**
	 * 计算出以每页6条信息展示所需要的总页数
	 * @return k--根据用户总数算出的总页数
	 */
	public int getApage(String ta_name){
		int k=0;
		int i=getAllUser(ta_name);//先获取数据库用户的总数
		if(i%6==0){//判断用户的总数是否能被6整除
			k=i/6;//能则    总页数=用户总数/6
		}else{
			k=(i/6)+1;//否则   总页数=（用户总数/6）+1  多出来的自然要再加一页
		}
		return k;
	}
	
	
	
	
	
	
}
